Power Query has a "Count Rows" button, but there's no equivalent button for when you want to quickly get the total of your values column, to see total sales or total users etc.
But there's good news! There are two relatively simple ways to do this. The one-step version for those happy to work in Advanced Editor, and the easier two-step version for those who aren't. Let's take them in reverse order.
The easier, two-step, version
1. Choose "Add Column">"Custom Column" and in the formula box just type "Total" (with quotes)
2. Now chose "Transform">"Group By". In the 1st box select your new column, under "Operation" choose "Sum" and under "Column" choose the column that has the values to be summed. Press OK to get the above!
The one-step Advanced Editor version
In a way this is even simpler. Just go to Advanced Editor, put a column on the end of the last line of code and press "Enter". On the new line type Total = List.Sum(Source[Users]). Change the very last line from Source to Total and press OK.
Hat tip to John Dalesandro's post Microsoft Power Query for Excel Tips and Tricks, which gave me enough to work the rest out. Should work for Power Query in both Power BI and Excel.
No comments:
Post a Comment